﻿/**
 *       Project: ServerLib
 *     Copyright: Copyright (C) 2008, Rob Kennedy
 *   Create Date: 2008-07-15 4:40 PM
 * Modified Date: 2008-07-15 4:40 PM    RK  Added comments
 *      Comments: Ignore SA1633.
 *      
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * any later version.
 * 
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */

namespace ServerLib.Data.Oracle
{
    using System;
    using System.Data;
    using System.Data.OracleClient;
    using ServerLib.Properties;

    /// -------------------------------------------------------------------- 
    /// Project : ServerLib 
    /// Class : MySqlDatabase
    /// Inherits : IDatabase
    /// -------------------------------------------------------------------- 
    /// <summary> 
    ///  An implementation of the IDatabase interface for mySQL
    ///  database systems utilizing ADO.net MySqlClient.
    /// </summary> 
    /// <authorname>Rob Kennedy</authorname> 
    /// <remarks> 
    /// </remarks> 
    /// --------------------------------------------------------------------
    public class OracleDatabase : IDatabase 
    {
        #region Private Properties

        /// <summary>
        /// Contains the database class unique name
        /// </summary>
        private string className = "Oracle";

        /// <summary>
        /// Contains the current SQL connection object
        /// </summary>
        private OracleConnection dataConnection;

        /// <summary>
        /// Contains a connection string object
        /// </summary>
        private ConnectionStringBuilder connectionString;

        /// <summary>
        /// Contains error message string data
        /// </summary>
        private string errorMessage;
        
        #endregion

        #region Public Constructors

        /// <summary>
        /// Initializes a SqlServerDatabase class
        /// </summary>
        public OracleDatabase()
            : this(string.Empty)
        {
        }

        /// <summary>
        /// Initializes a SqlServerDatabase class with the specified connection string
        /// </summary>
        /// <param name="connectionString">The connection string to configure</param>
        public OracleDatabase(string connectionString)
        {
            this.errorMessage = string.Empty;
            this.dataConnection = new OracleConnection();
            this.connectionString = new ConnectionStringBuilder(connectionString);
        }

        #endregion

        #region Public Properties

        /// <summary>
        /// Gets the unique database class interface name
        /// </summary>
        public string Name
        {
            get
            {
                return this.className;
            }
        }

        /// <summary>
        ///  Gets or sets the ConnectionStringBuilder Object
        /// </summary>
        public ConnectionStringBuilder ConnectionString
        {
            get
            {
                return this.connectionString;
            }

            set
            {
                this.connectionString = value;
            }
        }

        /// <summary>
        ///  Gets a value indicating whether the connection status is open
        /// </summary>
        public bool IsConnected
        {
            get
            {
                return (this.dataConnection != null) ? this.dataConnection.State == System.Data.ConnectionState.Open : false;
            }
        }

        /// <summary>
        ///  Gets any error message that may have been issued from the
        ///  database server
        /// </summary>
        public string ErrorMessage
        {
            get 
            { 
                return this.errorMessage; 
            }
        }

        /// <summary>
        ///  Gets or sets the current database catalog in use
        /// </summary>
        public string DatabaseName
        {
            get
            {
                string resultValue = string.Empty;

                if (this.dataConnection != null ? this.IsConnected : false)
                {
                    resultValue = this.dataConnection.Database;
                }

                return resultValue;
            }

            set
            {
                try
                {
                    if (this.dataConnection != null)
                    {
                        // if not the current catalog in use, change database
                        if (this.dataConnection.Database != value ? this.IsConnected : false)
                        {
                            this.dataConnection.ChangeDatabase(value);
                        }
                    }
                }
                catch (OracleException ex)
                {
                    this.errorMessage = ex.Message;
                }
            }
        }

        #endregion

        #region Public Methods

        /// <summary>
        ///  Opens a database connection
        /// </summary>
        /// <returns>returns true if connected</returns>
        public bool Open()
        {
            return this.Open(string.Empty);
        }
        
        /// <summary>
        ///  Opens a database connection using the specified connection string 
        /// </summary>
        /// <param name="connectionString">connection string to use for database connection</param>
        /// <returns>Returns true if connected</returns>
        public bool Open(string connectionString)
        {
            if (this.dataConnection != null)
            {
                if (connectionString.Length > 0)
                {
                    this.connectionString = ConnectionStringBuilder.CreateNewConnectionString(connectionString);
                }

                try
                {
                    this.dataConnection.ConnectionString = this.connectionString.ToString();
                    this.dataConnection.Open();
                }
                catch (OracleException ex)
                {
                    // set error message
                    this.errorMessage = ex.Message;
                }
            }

            return this.IsConnected;
        }

        /// <summary>
        ///  Closes a database connection
        /// </summary>
        public void Close()
        {
            if (this.dataConnection != null)
            {
                try
                {
                    if (this.dataConnection.State != ConnectionState.Closed)
                    {
                        this.dataConnection.Close();
                    }
                }
                catch (OracleException ex)
                {
                    // set error message
                    this.errorMessage = ex.Message;
                }
            }
        }

        /// <summary>
        ///  Queries an open database with the specified Sql command
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <returns>Returns a DataTable containing query results</returns>
        public DataTable SelectQuery(string sqlCommand)
        {
            DataTable dataResults = new DataTable();

            if (this.IsConnected)
            {
                try
                {
                    OracleDataAdapter dataAdapter = new OracleDataAdapter(sqlCommand, this.dataConnection);
                    dataAdapter.Fill(dataResults);
                }
                catch (OracleException ex)
                {
                    this.errorMessage = ex.Message;
                    dataResults = null;
                }
            }
            else
            {
                this.errorMessage = Resources.DatabaseNotConnected;
                dataResults = null;
            }

            return dataResults;
        }

        /// <summary>
        /// Queries an open database with the specified Sql command
        /// and injects additional parameter values into the tokenized Sql string. 
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <param name="parameterValues">Parameter values to inject into the Sql command string</param>
        /// <returns>Returns a DataTable, otherwise null on error</returns>
        public DataTable SelectQuery(string sqlCommand, params object[] parameterValues)
        {
            DataTable dataResults = new DataTable();

            if (this.IsConnected)
            {
                try
                {
                    sqlCommand = SqlCommandStringBuilder.InjectParameterValues(sqlCommand, parameterValues);

                    OracleDataAdapter dataAdapter = new OracleDataAdapter(sqlCommand, this.dataConnection);
                    dataAdapter.Fill(dataResults);
                }
                catch (OracleException ex)
                {
                    this.errorMessage = ex.Message; 
                    dataResults = null;
                }
            }
            else
            {
                this.errorMessage = Resources.DatabaseNotConnected;
                dataResults = null;
            }

            return dataResults;
        }

        /// <summary>
        /// Queries an open database with the specified Sql command returning a single
        /// DataRow result.
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <returns>Returns a DataRow result, otherwise null on error</returns>
        public DataRow SelectQueryRow(string sqlCommand)
        {
            return this.SelectQueryRow(sqlCommand, 0);
        }

        /// <summary>
        /// Queries an open database with the specified Sql command returning a single
        /// DataRow result.
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <param name="rowIndex">The specific row index of all rows returned from the query results</param>
        /// <param name="parameterValues">Parameter values to inject into the Sql command string</param>
        /// <returns>Returns a DataRow result, otherwise null on error</returns>
        public DataRow SelectQueryRow(string sqlCommand, int rowIndex, params object[] parameterValues)
        {
            DataRow returnRow = null;

            if (this.dataConnection != null ? this.IsConnected : false)
            {
                try
                {
                    DataTable dataResults = this.SelectQuery(sqlCommand, parameterValues);
                    
                    if (dataResults != null)
                    {
                        if (dataResults.Rows.Count - 1 >= rowIndex)
                        {
                            returnRow = dataResults.Rows[rowIndex];
                        }
                    }
                }
                catch (Exception ex)
                {
                    this.errorMessage = ex.Message; 
                }
            }
            else
            {
                this.errorMessage = Resources.DatabaseNotConnected;
            }

            return returnRow;
        }

        /// <summary>
        /// Shorthand command to query an open database with the specified Sql command returning the
        /// top most single DataRow result.
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <returns>Returns a DataRow result, otherwise null on error</returns>
        public DataRow SelectQueryTopRow(string sqlCommand)
        {
            return this.SelectQueryRow(sqlCommand);
        }

        /// <summary>
        /// Shorthand command to query an open database with the specified Sql command returning the
        /// top most single DataRow result.
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <param name="parameterValues">Parameter values to inject into the Sql command string</param>
        /// <returns>Returns a DataRow result, otherwise null on error</returns>
        public DataRow SelectQueryTopRow(string sqlCommand, params object[] parameterValues)
        {
            return this.SelectQueryRow(sqlCommand, 0, parameterValues);
        }
        
        /// <summary>
        ///  Queries an open database with the specified Sql command returning a boolean value for the
        ///  results returned.
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <param name="useRecordCount">If true, the return result is true if row count is greater than 0</param>
        /// <param name="parameterValues">Parameter values to inject into the Sql command string</param>
        /// <returns>Returns true if record count is greater than 0</returns>
        public bool QueryCheck(string sqlCommand, bool useRecordCount, params object[] parameterValues)
        {
            bool resultValue = false;

            if (this.dataConnection != null ? this.IsConnected : false)
            {
                try
                {
                    sqlCommand = SqlCommandStringBuilder.InjectParameterValues(sqlCommand, parameterValues);

                    OracleCommand queryCommand = new OracleCommand(sqlCommand, this.dataConnection);

                    if (useRecordCount)
                    {
                        resultValue = (queryCommand.ExecuteScalar() != null);
                    }
                    else
                    {
                        resultValue = Convert.ToBoolean(queryCommand.ExecuteScalar());
                    }
                    
                    queryCommand = null;
                }
                catch (OracleException ex)
                {
                    this.errorMessage = ex.Message; 
                }
            }
            else
            {
                this.errorMessage = Resources.DatabaseNotConnected;
            }

            return resultValue;
        }

        /// <summary>
        ///  Executes a query on an open database with the specified Sql command and returns
        ///  the number of rows effected.
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <param name="parameterValues">Parameter values to inject into the Sql command string</param>
        /// <returns>Returns the number of rows effected</returns>
        public int ExecuteQuery(string sqlCommand, params object[] parameterValues)
        {
            int resultValue = 0;

            if (this.dataConnection != null ? this.IsConnected : false)
            {
                try
                {
                    sqlCommand = SqlCommandStringBuilder.InjectParameterValues(sqlCommand, parameterValues);

                    OracleCommand queryCommand = new OracleCommand(sqlCommand, this.dataConnection);
                    resultValue = queryCommand.ExecuteNonQuery();
                    queryCommand = null;
                }
                catch (OracleException ex)
                {
                    this.errorMessage = ex.Message; 
                }
            }
            else
            {
                this.errorMessage = Resources.DatabaseNotConnected;
            }

            return resultValue;
        }

        /// <summary>
        /// Queries an open database with the specified Sql command returning the value
        /// of the specified field of the first row result.
        /// </summary>
        /// <param name="sqlCommand">The Sql command query to execute</param>
        /// <param name="valueField">The name of the result field to retrive a value from</param>
        /// <param name="parameterValues">Parameter values to inject into the Sql command string</param>
        /// <returns>Returns a value of the specified field of the first row result.</returns>
        public object QueryValue(string sqlCommand, string valueField, params object[] parameterValues)
        {
            object resultValue = null;

            if (this.dataConnection != null ? this.IsConnected : false)
            {
                try
                {
                    DataTable resultData = this.SelectQuery(sqlCommand, parameterValues);
                   
                    if (resultData != null)
                    {
                        if (resultData.Rows.Count > 0)
                        {
                            if (valueField.Length > 0)
                            {
                                resultValue = resultData.Rows[0][valueField];
                            }
                            else
                            {
                                resultValue = resultData.Rows[0][0];
                            }
                        }
                    }
                }
                catch (OracleException ex)
                {
                    this.errorMessage = ex.Message; 
                }
            }
            else
            {
                this.errorMessage = Resources.DatabaseNotConnected;
            }

            return resultValue;
        }

        /// <summary>
        ///  Not implemented for Oracle
        /// </summary>
        /// <param name="tableName">The name of the table to retrieve the identity from</param>
        /// <returns>Not implemented and therefore returns 0</returns>
        public int GetCurrentIdentity(string tableName)
        {
            return 0;
        }
        #endregion
    }
}
